package com.pc.admin.test.tenThreadInsert;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Author: ZhangJing
 * @Date: 2023-09-06  16:47
 */

/**
 * 用多线程向数据库插入一百万条数据
 */
public class MyThread extends Thread {
    @Override
    public void run() {
        String url = "jdbc:mysql://127.0.0.1/teacher?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String name = "com.mysql.cj.jdbc.Driver";
        String user = "root";
        String password = "root";
        Connection conn = null;
        try {
            Class.forName(name);
            //获取连接
            conn = DriverManager.getConnection(url, user, password);
            //关闭自动提交，不然conn.commit()运行到这句会报错
            conn.setAutoCommit(false);
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 开始时间
        long begin = System.currentTimeMillis();
        // sql前缀
        String prefix = "INSERT INTO test_teacher (t_name,t_password,sex,description,pic_url,school_name,regist_date,remark) VALUES ";
        try {
            // 保存sql后缀
            StringBuffer suffix = new StringBuffer();
            // 设置事务为非自动提交
            conn.setAutoCommit(false);
            // 比起st，pst会更好些  //准备执行语句
//            PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");
//            PreparedStatement pst = (PreparedStatement) conn.prepareStatement(suffix.toString());
            // 外层循环，总提交事务次数
            PreparedStatement pst = null;
            for (int i = 1; i <= 10; i++) {
                // 第j次提交步长
                for (int j = 1; j <= 100000; j++) {
                    // 构建SQL后缀
                    suffix.append("(’" + i * j + "’，‘123456’" + ",‘男’" + ",‘教师’" + ",‘www.bbb.com’" + ",‘Java大学’" + ",’" + "2016 - 08 - 16 14:43:26" + "’，‘备注’" + "),");
                }
            }
// 构建完整SQL
            String sql = prefix + suffix.substring(0, suffix.length() - 1);
// 添加执行SQL
            pst = (PreparedStatement) conn.prepareStatement(sql);
// 执行操作
            pst.executeBatch();
// 提交事务
            conn.commit();
// 清空上一次添加的数据
            suffix.setLength(0);


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 结束时间
        long end = System.currentTimeMillis();
        // 耗时
        System.out.println("100万条数据插入花费时间 : " + (end - begin) / 1000 + " s" + "插入完成");
    }
}